"""
Database Models for TaskMaster v1
AceFlow v3.0 - Implementation Phase

Database Schema:
- users: User account information
- tasks: User tasks with data isolation
"""

import duckdb
from datetime import datetime
from typing import Optional
from pydantic import BaseModel


class User(BaseModel):
    """User model for authentication and data isolation"""
    id: Optional[int] = None
    username: str
    password_hash: str
    email: Optional[str] = None
    avatar: Optional[str] = None
    created_at: Optional[datetime] = None


class Task(BaseModel):
    """Task model with user isolation"""
    id: Optional[int] = None
    user_id: int
    title: str
    description: Optional[str] = None
    due_date: datetime
    priority: int  # 1=High, 2=Medium, 3=Low
    status: str  # pending, in_progress, completed
    created_at: Optional[datetime] = None
    notified: bool = False


class DatabaseManager:
    """DuckDB database manager for TaskMaster"""

    def __init__(self, db_path: str = "taskmaster.db"):
        self.db_path = db_path
        self._init_database()

    def _init_database(self):
        """Initialize database tables"""
        with duckdb.connect(self.db_path) as conn:
            # Create users table
            conn.execute("""
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY,
                    username VARCHAR UNIQUE NOT NULL,
                    password_hash VARCHAR NOT NULL,
                    email VARCHAR,
                    avatar VARCHAR,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)

            # Create tasks table
            conn.execute("""
                CREATE TABLE IF NOT EXISTS tasks (
                    id INTEGER PRIMARY KEY,
                    user_id INTEGER NOT NULL,
                    title VARCHAR NOT NULL,
                    description TEXT,
                    due_date TIMESTAMP NOT NULL,
                    priority INTEGER NOT NULL CHECK (priority IN (1, 2, 3)),
                    status VARCHAR NOT NULL DEFAULT 'pending'
                        CHECK (status IN ('pending', 'in_progress', 'completed')),
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    notified BOOLEAN DEFAULT FALSE,
                    FOREIGN KEY (user_id) REFERENCES users(id)
                )
            """)

            # Create indexes for performance
            conn.execute("CREATE INDEX IF NOT EXISTS idx_tasks_user_id ON tasks(user_id)")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_tasks_due_date ON tasks(due_date)")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status)")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)")

    def get_connection(self):
        """Get database connection"""
        return duckdb.connect(self.db_path)


# Global database instance
db_manager = DatabaseManager()


def get_db_manager() -> DatabaseManager:
    """Get database manager instance"""
    return db_manager
